SQL Window Functions

SQL Tutorial Home

This lesson introduces you to SQL window functions. They are a powerful kind of function, that computes a result based on a set of rows, instead of computing based on a single row. They are similar to aggregate functions. The term window refers to the set of rows used to calculate the result of the function. This lesson focuses on how window functions work and the syntax to use.

SQL Window functions enable you to calculate functions including: average, count, max, and min on a group of records. The individual records are not collapsed, so you can create a query combining or showing the individual record together with the result of a window function. This ability to combine record level fields with results of functions applied to a group of records is what makes windows functions so powerful.

Understanding How SQL Window Functions Work

Here is an example using the Employee table:

employee_id full_name department salary
100 Mary Johns SALES 1000.00
101 Sean Moldy IT 1500.00
102 Peter Dugan SALES 2000.00
103 Lilian Penn SALES 1700.00
104 Milton Kowarsky IT 1800.00
105 Mareen Bisset ACCOUNTS 1200.00
106 Airton Graue ACCOUNTS 1100.00

This example uses window functions to find the salary of each employee and the highest salary in his/her department. The result is the employee name, salary, and the top salary of the department where he/she works.


SELECT  full_name,
        salary,
        department
        MAX(salary) OVER (PARTITION BY department) as top_salary
FROM    employee

The PARTITION BY clause in the query creates groups of records (the window) having the same value in the column department. The MAX() function finds the maximum value of salary in the window. Here is the result:

full_name department salary top_salary
Mary Johns SALES 1000.00 2000.00
Sean Moldy IT 1500.00 1800.00
Peter Dugan SALES 2000.00 2000.00
Lilian Penn SALES 1700.00 2000.00
Milton Kowarsky IT 1800.00 1800.00
Mareen Bisset ACCOUNTS 1200.00 1200.00
Airton Graue ACCOUNTS 1100.00 1200.00

Positional SQL Window Functions

When you compare window functions vs aggregate functions, you find a powerful feature on window functions side: positional functions. They can obtain a column value from other records in the same window, which enables you to create complex reports in a few SQL lines. Two of these functions are lead() and lag():

  • lead() returns a column value of the previous record in the window.
  • lag() returns a column value from the next record in the window.

To use these functions it is important to have the window ordered by any column in the table.

Here is a table (share) to store shares from companies with their market values at some point in time:

share_symbol timestamp value
OILBEST 2020-03-05 10:00 120
OILBEST 2020-03-05 12:00 123
OILBEST 2020-03-05 15:00 122
BANKWEB 2020-03-05 10:00 91
BANKWEB 2020-03-05 12:00 87
BANKWEB 2020-03-05 15:00 99

Suppose you want a report with the value of every share with the previous value and the variation percentage. You can use the lead() function to obtain the previous value of the share. Note the importance of having the window ordered by timestamp. If the window is not ordered by timestamp, the results are wrong.


SELECT share_symbol,
       timestamp,
       value,
       lag(value) OVER (PARTITION BY share_symbol ORDER BY timestamp ) 
              AS previous_value,
       trunc(((value - (lag(value) OVER (PARTITION BY share_symbol ORDER BY timestamp )))*100)/value,2) 
              AS percentage_variation
FROM share

Here is the result:
Query Result

The query result columns previous_value and percentage_variation are calculated columns using values from different records in the same table, this is really powerful!

There are more window functions such as rank(), first_value, nth_value() and last_value(), which are explored later.

Closing Words

SQL Window functions are one of the most unknown parts of the SQL language. As you saw in this article, they are powerful and flexible. There are clauses (for example PARTITION BY or WINDOW FRAME) and topics that were not covered in this article. Keep going, learn SQL and increase your skills!

IN THIS PAGE